package com.tdam.CommunicationManager;



import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.MatrixCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

class DbInterface extends SQLiteOpenHelper
{
	public static final String MIMETYPE_WEBMAIL = "vnd.android.cursor.item/webmail";
	private static final String DATABASE_NAME = "WebMessages.db";
	private static final int DATABASE_VERSION = 1;
	private static final String WEB_MESSAGES_TABLE = "messages";
	private static final String USERS_TABLE = "users";
	private Resources _resources;
	private SimpleDateFormat formater;
	 

	public DbInterface(Context context)
	{
		super(context, DATABASE_NAME, null,DATABASE_VERSION );
		formater=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	}
	
	@Override
	public void onCreate(SQLiteDatabase db)
	{
		
		createDatabaseSchema(db);
	}


	private void createDatabaseSchema(SQLiteDatabase db) {
		//db.execSQL("drop table messages");
		StringBuilder createTableWebMessages = new StringBuilder();
		createTableWebMessages.append("CREATE  TABLE  IF NOT EXISTS ");
		createTableWebMessages.append(WEB_MESSAGES_TABLE);
		createTableWebMessages.append("(\"");
		createTableWebMessages.append(WebMessageCursor.COLUMN_ID);
		createTableWebMessages.append("\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"");
		createTableWebMessages.append(WebMessageCursor.COLUMN_USER_NAME);
		createTableWebMessages.append("\" TEXT NOT NULL, \"");
		createTableWebMessages.append(WebMessageCursor.COLUMN_FRIEND_NAME);
		createTableWebMessages.append("\" TEXT NOT NULL , \"");
		createTableWebMessages.append(WebMessageCursor.COLUMN_TIMESTAMP);
		createTableWebMessages.append("\" TEXT , \"");
		createTableWebMessages.append(WebMessageCursor.COLUMN_DIRECTION);
		createTableWebMessages.append("\" INTEGER , \"");
		createTableWebMessages.append(WebMessageCursor.COLUMN_MESSAGE);
		createTableWebMessages.append("\" TEXT )");
		db.execSQL(createTableWebMessages.toString());

		
		StringBuilder createTableUsers= new StringBuilder();
		createTableUsers.append("CREATE  TABLE  IF NOT EXISTS ");
		createTableUsers.append(USERS_TABLE);
		createTableUsers.append("(\"");
		createTableUsers.append(UserCursor.COLUMN_ID);
		createTableUsers.append("\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , \"");
		createTableUsers.append(UserCursor.COLUMN_USER_NAME);
		createTableUsers.append("\" TEXT , \"");
		createTableUsers.append(UserCursor.COLUMN_PASSWORD);
		createTableUsers.append("\" TEXT )");
		db.execSQL(createTableUsers.toString());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public void registerUser(String user,String password)
	{
		ContentValues row;
		row=new ContentValues();
		row.put(UserCursor.COLUMN_USER_NAME,user);
		row.put(UserCursor.COLUMN_PASSWORD,password);
		getWritableDatabase().insert(USERS_TABLE,null,row);
	}
	
	public boolean confirmIdentity(String user,String password)
	{
		
		Cursor cursor = getReadableDatabase().query(USERS_TABLE,null,
				UserCursor.COLUMN_USER_NAME + "=? AND "
				+ UserCursor.COLUMN_PASSWORD  + "=?",
				new String[]{ user, password},null,null,null);
		boolean response=false;
		if(!cursor.isClosed())
			{
				response=cursor.moveToNext();
				cursor.close();
			}
		
		return response;
		//return true;
	}
	
	public ArrayList<WebMailInfo> getMessages(String friendUser)
	{

		ArrayList<WebMailInfo> messages=new ArrayList<WebMailInfo>();
		Cursor cursor = getReadableDatabase().query(WEB_MESSAGES_TABLE,null,
				WebMessageCursor.COLUMN_USER_NAME + "=? AND "
				+ WebMessageCursor.COLUMN_FRIEND_NAME  + "=?",
				new String[]{ User.name, friendUser },null,null,
				WebMessageCursor.COLUMN_TIMESTAMP + " ASC");
		try
		{
			while(cursor.moveToNext())
			{
				WebMailInfo message=new WebMailInfo(
				cursor.getString(cursor.getColumnIndex(WebMessageCursor.COLUMN_FRIEND_NAME)),
				cursor.getString(cursor.getColumnIndex(WebMessageCursor.COLUMN_MESSAGE)),
				formater.parse(cursor.getString(cursor.getColumnIndex(WebMessageCursor.COLUMN_TIMESTAMP))),
				(cursor.getInt(cursor.getColumnIndex(WebMessageCursor.COLUMN_DIRECTION))==1)?true:false);
				messages.add(message);
			}
		}
		catch(ParseException e)
		{
			
		}
		if(!cursor.isClosed())cursor.close();

		return messages;
	}
	
	public void deleteMessages(String friendUser)
	{
//		try
//		{
			getWritableDatabase().delete(WEB_MESSAGES_TABLE,
					WebMessageCursor.COLUMN_USER_NAME + "=? AND "
					+ WebMessageCursor.COLUMN_FRIEND_NAME  + "=?",
					new String[]{ User.name, friendUser });
//		}
//		catch(Exception e)
//		{
//			
//		}
	
	}

	public void deleteSingleMessage(WebMailInfo message)
	{
//		try
//		{
		
		
			getWritableDatabase().delete(WEB_MESSAGES_TABLE,
					WebMessageCursor.COLUMN_USER_NAME + "=? AND "
					+ WebMessageCursor.COLUMN_FRIEND_NAME  + "=? AND "
					+ WebMessageCursor.COLUMN_TIMESTAMP + "=?",
					new String[]{ User.name, message.user,formater.format(message.timeStamp) });
//		}
//		catch(Exception e)
//		{
//			
//		}
	
	}
	
	
	
	public ArrayList<WebMailInfo> getLastMessageForContacts()
	{

		ArrayList<WebMailInfo> messages=new ArrayList<WebMailInfo>();
		Cursor cursor = getReadableDatabase().query(WEB_MESSAGES_TABLE,null,
				WebMessageCursor.COLUMN_USER_NAME + "=? AND "
				+ WebMessageCursor.COLUMN_TIMESTAMP + " IN (select MAX(m." 
				+ WebMessageCursor.COLUMN_TIMESTAMP + ") from " + WEB_MESSAGES_TABLE
				+ " m where m." + WebMessageCursor.COLUMN_USER_NAME + "=?"
				+ " group by " +  WebMessageCursor.COLUMN_FRIEND_NAME + ")",
				new String[]{ User.name ,User.name },null,null,WebMessageCursor.COLUMN_TIMESTAMP + " DESC");
		
		try
		{
			while(cursor.moveToNext())
			{
				WebMailInfo message=new WebMailInfo(
				cursor.getString(cursor.getColumnIndex(WebMessageCursor.COLUMN_FRIEND_NAME)),
				cursor.getString(cursor.getColumnIndex(WebMessageCursor.COLUMN_MESSAGE)),
				formater.parse(cursor.getString(cursor.getColumnIndex(WebMessageCursor.COLUMN_TIMESTAMP))),
				(cursor.getInt(cursor.getColumnIndex(WebMessageCursor.COLUMN_DIRECTION))==1)?true:false);
				messages.add(message);
			}
		}
		catch(ParseException e)
		{
			
		}
		if(!cursor.isClosed())cursor.close();

		return messages;
	}
	
	public void saveMessages(ArrayList<WebMailInfo> messages)
	{

		ContentValues row;
		for(WebMailInfo message : messages)
		{
			row=new ContentValues();
			row.put(WebMessageCursor.COLUMN_USER_NAME,User.name);
			row.put(WebMessageCursor.COLUMN_FRIEND_NAME,message.user);
			row.put(WebMessageCursor.COLUMN_TIMESTAMP, formater.format(message.timeStamp));
			row.put(WebMessageCursor.COLUMN_DIRECTION, (message.direction)?1:0);
			row.put(WebMessageCursor.COLUMN_MESSAGE,message.message);
			getWritableDatabase().insert(WEB_MESSAGES_TABLE,null,row);
		}

	}

	class WebMessageCursor  extends MatrixCursor
	{
		public static final String COLUMN_ID = android.provider.BaseColumns._ID;
		public static final String COLUMN_USER_NAME = "userName";
		public static final String COLUMN_FRIEND_NAME= "friendName";
		public static final String COLUMN_TIMESTAMP = "timestamp";
		public static final String COLUMN_DIRECTION = "direction";
		public static final String COLUMN_MESSAGE = "message";
		
		public WebMessageCursor()
		{
			super(new String[] { COLUMN_ID ,COLUMN_USER_NAME, COLUMN_FRIEND_NAME,
					COLUMN_TIMESTAMP,COLUMN_DIRECTION,COLUMN_MESSAGE });
		}
	}
	class UserCursor  extends MatrixCursor
	{
		public static final String COLUMN_ID= android.provider.BaseColumns._ID;
		public static final String COLUMN_USER_NAME = "userName";
		public static final String COLUMN_PASSWORD = "password";
		
		public UserCursor()
		{
			super(new String[] {COLUMN_ID,COLUMN_USER_NAME,COLUMN_PASSWORD});
		}
	
//		public void add(String user, WebMailInfo webMessage) 
//		{
//			if (webMessage!= null) 
//				addRow(new Object[] { user,webMessage.user,webMessage.timeStamp.toString(),
//						(webMessage.direction)?1:0 ,webMessage.message});
//		}
	}

}